package dao.Impl;

import dao.EmpDao;
import entity.Emp;
import utils.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

public class EmpDaoImpl implements EmpDao {

    @Override
    public int insertEmp(Emp emp) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement prepareStatement = connection.prepareStatement("insert into emp values (?,?,?,?,?,?,?,?)");
        prepareStatement.setInt(1,emp.getEmpno());
        prepareStatement.setString(2,emp.getEname());
        prepareStatement.setString(3,emp.getJob());
        prepareStatement.setInt(4,emp.getMgr());
        prepareStatement.setDate(5,new Date(emp.getHiredate().getTime()));
        prepareStatement.setDouble(6,emp.getSal());
        prepareStatement.setDouble(7,emp.getComm());
        prepareStatement.setInt(8,emp.getDeptno());

        int count = prepareStatement.executeUpdate();
        JDBCUtils.closeAll(connection,prepareStatement,null);

        return count;
    }

    @Override
    public int deleteEmp(int empno) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement prepareStatement = connection.prepareStatement("delete from emp where empno = ?");
        prepareStatement.setInt(1,empno);
        return prepareStatement.executeUpdate();
    }

    @Override
    public int updateEmp(Emp emp) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement prepareStatement = connection.prepareStatement("update emp set ename = ?,job = ?,mgr = ?,hiredate = ?,sal = ?,comm = ?,deptno = ? where empno = ?");
        prepareStatement.setString(1,emp.getEname());
        prepareStatement.setString(2,emp.getJob());
        prepareStatement.setInt(3,emp.getMgr());
        prepareStatement.setDate(4,new Date(emp.getHiredate().getTime()));
        prepareStatement.setDouble(5,emp.getSal());
        prepareStatement.setDouble(6,emp.getComm());
        prepareStatement.setInt(7,emp.getDeptno());
        prepareStatement.setInt(8,emp.getEmpno());
        int result = prepareStatement.executeUpdate();

        return result;
    }

    @Override
    public List<Emp> selectAll() throws SQLException {
        List<Emp> emps = new ArrayList<>();
        Connection connection = JDBCUtils.getConnection();
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select * from emp");
        while (resultSet.next()){
            Emp emp = new Emp();
            emp.setEmpno(resultSet.getInt("empno"));
            emp.setEname(resultSet.getString("ename"));
            emp.setJob(resultSet.getString("job"));
            emp.setMgr(resultSet.getInt("mgr"));
            emp.setHiredate(resultSet.getDate("hiredate"));
            emp.setSal(resultSet.getDouble("sal"));
            emp.setComm(resultSet.getDouble("comm"));
            emp.setDeptno(resultSet.getInt("deptno"));

            emps.add(emp);
        }
        return emps;
    }

    @Override
    public Emp selectOne(int empno) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement prepareStatement = connection.prepareStatement("select * from emp where empno = ?");
        prepareStatement.setInt(1,empno);
        ResultSet result = prepareStatement.executeQuery();
        Emp emp = new Emp();
        while (result.next()){
            emp.setEmpno(empno);
            emp.setEname(result.getString("ename"));
            emp.setJob(result.getString("job"));
            emp.setMgr(result.getInt("mgr"));
            emp.setHiredate(new java.util.Date(result.getDate("hiredate").getTime()));
            emp.setSal(result.getDouble("sal"));
            emp.setComm(result.getDouble("comm"));
            emp.setDeptno(result.getInt("deptno"));

        }
        return emp;
    }
}
